-- This demo shows the effect on case- and accent-sensitivity, both on data
-- and metadata.
-- © Erland Sommarskog 2024.

-- Start with a case-insensitive database.
USE Latin1_General_100_CI_AS
go
-- Plain string comparison.
SELECT IIF(N'insert' = N'INSERT', 'Equal', 'Different')
go

-- What is you server collation? When I do this demo it is case-sensitive, but
-- your server may be different.
SELECT serverproperty('Collation')
go
-- This will succeed if your collation is case-insensitive.
DECLARE @a int = 12
SELECT @A
go
-- And in that case, this will fail. Else this illustrates that data in 
-- variables always follow the database collation.
DECLARE @a char(3) = 'aaa',
        @A char(3) = 'AAA'
SELECT IIF(@a = @A, 'Equal', 'Not equal')
go

-- Create a table with mixed case and columns in lower-case, then access it
-- with different case.
CREATE TABLE SomeTable (Id int, cString varchar(20))
go
SELECT ID, cstring FROM sometable
go
-- This fails! Nothing strange, if you come from Pascal, Basic etc.
CREATE TABLE SOMETABLE (a varchar(20), b date)
go   


-- Now create a database with a case-sensitive collation and move there.
CREATE DATABASE Latin1_General_100_CS_AS COLLATE Latin1_General_100_CS_AS
go
USE Latin1_General_100_CS_AS
go
-- Same tests as above - but outcome is different.
SELECT IIF(N'insert' = N'INSERT', 'Equal', 'Different')
go
CREATE TABLE SomeTable (Id int, cString numeric(20,2))
go
SELECT ID, cstring FROM sometable
go
SELECT ID, cstring FROM SomeTable
go
CREATE TABLE SOMETABLE (a varchar(20), b date)
-- The outcome for the table and column names are perfectly normal if you
-- come from Python, Perl or C#.
go

-- Let's try a case-insensitive database for Hungarian.
CREATE DATABASE Hungarian_CI_AS COLLATE Hungarian_CI_AS
go
USE Hungarian_CI_AS
go
-- So far so good....
SELECT IIF(N'insert' = N'INSERT', 'Equal', 'Different')
CREATE TABLE SomeTable (Id int, cString varchar(20))
go
-- But this fails! As we have learnt, CS counts a letter of its own in 
-- Hungarian, so "cs" is that letter. But cS is "c" + "S".
SELECT ID, cstring FROM sometable
go
-- Now create a case-insensitive database for Turkish.
CREATE DATABASE Turkish_CI_AS COLLATE Turkish_CI_AS
go
USE Turkish_CI_AS
go
-- The result is surprising.
SELECT IIF(N'insert' = N'INSERT', 'Equal', 'Different')
go
-- Look! The dot on the lower-case i has moved!
SELECT upper(N'insert'), lower(N'INSERT')
go
-- Create a table with an uppercase name and access it in lowercase. Oops!
CREATE TABLE Instruments (a int NOT NULL)
go
SELECT a FROM instruments
go
-- Lesson: when developing for an international market, go with a case-sensitive
-- collation, both on server and database level - or forget the Turkish market.

-- Now create an accent-insensitive database and move there.
CREATE DATABASE Finnish_Swedish_CI_AI COLLATE Finnish_Swedish_CI_AI
go
USE Finnish_Swedish_CI_AI
go
-- Create a table that accepts a pair of words, with comparison columns 
-- according to German, Croatian and Swedish.
CREATE TABLE AccentInsensitive(
             id        int          NOT NULL,
             leftword  nvarchar(20) NOT NULL,
             rightword nvarchar(20) NOT NULL,
             German AS IIF(leftword  COLLATE Latin1_General_CI_AI = 
                           rightword COLLATE Latin1_General_CI_AI, 
                           '=', '<>'),
             Croatian  AS IIF(leftword  COLLATE Croatian_CI_AI =
                              rightword COLLATE Croatian_CI_AI,
                              '=', '<>'),
             Swedish AS IIF(leftword = rightword, '=', '<>'),
             CONSTRAINT pk_AccentSensitive PRIMARY KEY (id)
)

-- Add some words. There are gaps in the ids by purpose.
INSERT AccentInsensitive(id, leftword, rightword) VALUES
   (1,  N'resume',    N'résumé'),    -- Two different English words.
   (19, N'ćevapčići', N'cevapcici'), -- A Croatian word, properly spelled to the left, mutilated to the right.
   (37, N'cooperate', N'coöperate'), -- Two alternate spellings of the same English word - but one glyph happens to agree with a character in Swedish.
   (55, N'vetter',    N'wetter'),    -- Two different German words.
   (73, N'Müller',    N'myller')     -- A German name and Swedish for "throng".

-- These blanks lines are only to make it possible to display one pair
-- at a time in a live demo. No need to add if you run at home.
INSERT AccentInsensitive(id, leftword, rightword)
  SELECT value, '', ''
  FROM   generate_series(2, 80)
  WHERE  value % 18 <> 1

SELECT * FROM AccentInsensitive ORDER BY id
go
